with tmp_network as (
    select
        case when network_type = 3 then 1
             when network_type = 4 then 2
             when network_type = 5 then 3
             when network_type = 6 then 4
             when network_type = 2 then 5
             when network_type = 1 then 6
             else 0
        end as network_type
        ,code
        ,name as network_name
        ,agent_code
        ,agent_name
        ,fran_code as franchisee_code
        ,fran_name as franchisee_name
        ,center_code as transcenter_code
        ,center_name as transcenter_name
    from jms_dim.dim_network_whole_massage
)

insert overwrite table jms_dwd.dwd_tab_barscan_centerarrival_base_dt partition(dt)
select
tmp_barscan.recordid as record_id
,tmp_barscan.billcode as waybill_no
,tmp_barscan.listcode as list_code
,tmp_barscan.subbillcode as subbill_code
,tmp_barscan.transfercode as transfer_code
,tmp_barscan.packagecode as package_code
,tmp_barscan.scantype as scan_type
,tmp_barscan.nextstation as pre_station
,tmp_barscan.destination as destination
,tmp_barscan.scanuser as scan_user
,tmp_barscan.inputsite as scan_site
,tmp_barscan.scantime as scan_time
,tmp_barscan.inputtime as input_time
,tmp_barscan.operatedate as operate_date
,tmp_barscan.send_deliver_user as send_deliver_user
,tmp_barscan.sendcustomer as send_customer
,tmp_barscan.sendsite as send_site
,tmp_barscan.destsite as dest_site
,tmp_barscan.transfercenter as transfer_center
,tmp_barscan.pcs as pcs
,tmp_barscan.weight as weight
,tmp_barscan.goodstype as good_type
,tmp_barscan.expresstype as transport_type
,tmp_barscan.shifts as shifts
,tmp_barscan.transfer_deliver_fee as transfer_deliver_fee
,tmp_barscan.networkfee as network_manage_fee
,tmp_barscan.otherfee as other_fee
,tmp_barscan.checkflag as check_flag
,tmp_barscan.accountflag as match_oper_source
,tmp_barscan.receiptflag as receipt_flag
,tmp_barscan.returnflag as return_flag
,tmp_barscan.elescaleflag as elescale_flag
,tmp_barscan.unrecordflag as weight_flag
,tmp_barscan.writebackflag as writeback_flag
,tmp_barscan.lockflag as auto_type_flag
,tmp_barscan.remark as width
,tmp_barscan.modifyuser as station_num
,tmp_barscan.modifytime as modify_time
,tmp_barscan.mobile as length
,tmp_barscan.pistolid as pistol_id
,tmp_barscan.remark1 as height
,tmp_barscan.remark2 as flight_num
,tmp_barscan.scansitecode as scan_site_code
,tmp_barscan.uploadtime as upload_time
,tmp_barscan.remark3 as len_wid_hei_product
,tmp_barscan.scanusercode as scan_user_code
,tmp_barscan.send_deliver_usercode as send_deliver_usercode
,tmp_barscan.nextstationcode as pre_station_code
,tmp_barscan.source as source
,tmp_barscan.remark5 as remark5
,tmp_barscan.remark6 as remark6
,tmp_barscan.remark4 as list_code2
,tmp_barscan.scansiteid as scan_site_id
,tmp_barscan.nextstationid as pre_station_id
,tmp_barscan.scanuserid as scan_user_id
,tmp_barscan.send_deliver_userid as send_deliver_userid
,tmp_network.network_type as network_type
,tmp_network.agent_code as  site_agent_code
,tmp_network.agent_name  as site_agent_name
,tmp_network.franchisee_code as site_franchisee_code
,tmp_network.franchisee_name as site_franchisee_name
,tmp_network.transcenter_code as site_transcenter_code
,tmp_network.transcenter_name as site_transcenter_name
,tmp_network_pre.network_type as pre_network_type
,case
when (length(billcode) = 13) then 'GB'
when (length(billcode) = 15 and billcode like "55%") then 'BS'
when (length(billcode) = 12 and (billcode like "40%" or billcode like "42%" )) then 'BSB'
when (length(billcode) = 15 and billcode like "JT%") then 'JT'
when (length(billcode) = 13 and billcode like "B%") then 'JTB'
else "Others"
end as waybill_source
,tmp_barscan.dt as dt
 from jms_ods.tab_barscan_centerarrival tmp_barscan
 left join tmp_network tmp_network on tmp_barscan.scansitecode = tmp_network.code
 left join tmp_network tmp_network_pre on tmp_barscan.nextstationcode = tmp_network_pre.code where tmp_barscan.dt='{{ execution_date | cst_ds }}' ;